using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Xml;

namespace WebTools.Data
{
	#region Enums
	/// <summary>
	/// An enumeration used to specify the ordering direction of an ORDER BY SQL statement.
	/// </summary>
	public enum OrderByDirection
	{
		/// <summary>
		/// Specifies that the ordering should be in ASCENDING order.
		/// </summary>
		Acs,
		/// <summary>
		/// Specifies that the ordering should be in DESCENDING order.
		/// </summary>
		Desc,
	}
	#endregion

	#region XQuery Class
	#region XmlDoc
	/// <summary>
	/// Provides the methods and properties for building a SQL statement that can query the Xml Data Type column of an MS SQL database.
	/// </summary>
	/// <remarks>
	/// <para>
	/// Currently the XQuery that is executed at the database uses the exist() method of the XML column. Support for the other query 
	/// methods such as query() and value() may be added but exist() provides sufficient functionality for the XQuery class.
	/// </para>
	/// <para>
	/// When using the XQuery class against an XML data type in your SQL 2005 database be sure to add a primary XML index and 
	/// a secondary PATH index.
	/// <code>
	/// <![CDATA[
	/// SET ARITHABORT ON -- Required for indexed xml type columns.
	/// GO
	/// CREATE TABLE dbo.webtools_Objects
	/// (
	/// 	ObjectId	int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	/// 	TypeName	nvarchar(256),
	/// 	ObjectXml	xml NOT NULL
	/// ) 
	/// GO
	/// CREATE PRIMARY XML INDEX xml_ix_webtools_Objects_ObjectXml ON dbo.webtools_Objects(ObjectXml)
	/// GO
	/// CREATE XML INDEX xml_ix_webtools_Objects_ObjectXml_PATH ON dbo.webtools_Objects(ObjectXml) USING XML INDEX xml_ix_webtools_Objects_ObjectXml FOR PATH
	/// GO
	/// ]]>
	/// </code>
	/// </para>
	/// 
	/// </remarks>
	#endregion
	public class XQuery
	{
		/* ===============================================================
		 * QUERY CONSTRUCT:
		 * ===============================================================
		 * SELECT
		 *		[COLUMN_LIST]
		 * FROM
		 *		[TABLE_NAME]
		 * WHERE
		 *		[COLUMN] = @PARAMETER
		 *		AND [XML_COLUMN.EXIST()] = 1
		 */

		/// <summary>
		/// Creates a new instance of the XQuery class.
		/// </summary>
		/// <param name="schema">The TableSchema instance that will define the query.</param>
		public XQuery(TableSchema schema)
		{
			_schema = schema;
		}

		#region AddWhere
		/// <summary>
		/// Adds a new Where clause to the current query. Used for parameterized queries on normal sql columns.
		/// </summary>
		/// <param name="columnName">The name of the column.</param>
		/// <param name="value">The value to compare against the table column values.</param>
		public void AddWhere(string columnName, object value)
		{
			this.AddWhere(new Where(columnName, Comparison.Equals, value));
		}
		/// <summary>
		/// Adds a new Where clause to the current query. Used for parameterized queries on normal sql columns.
		/// </summary>
		/// <param name="columnName">The name of the column.</param>
		/// <param name="comparison">One of the Comparison values.</param>
		/// <param name="value">The value to compare against the table column values.</param>
		public void AddWhere(string columnName, Comparison comparison, object value)
		{
			this.AddWhere(new Where(columnName, comparison, value));
		}
		/// <summary>
		/// Adds a new Where clause to the current query. Used for parameterized queries on normal sql columns.
		/// </summary>
		/// <param name="where">An instance of a Where clause class.</param>
		public void AddWhere(Where where)
		{
			_wheres.Add(where);
		}
		#endregion

		#region AddXWhere
		/// <summary>
		/// Adds a new Where clause to the current query. Used for parameterized queries on xml sql columns.
		/// </summary>
		/// <param name="xmlColumn">The name of the column that is of type (xml).</param>
		/// <param name="path">The XPath query to the node within the xml data type column to use for comparison.</param>
		/// <param name="value">The value to compare with the xml node value.</param>
		public void AddXWhere(string xmlColumn, string path, object value)
		{
			this.AddXWhere(new XWhere(xmlColumn, path, Comparison.Equals, value));
		}
		/// <summary>
		/// Adds a new Where clause to the current query. Used for parameterized queries on xml sql columns.
		/// </summary>
		/// <param name="xmlColumn">The name of the column that is of type (xml).</param>
		/// <param name="path">The XPath query to the node within the xml data type column to use for comparison.</param>
		/// <param name="comparison">One of the Comparison values.</param>
		/// <param name="value">The value to compare with the xml node value.</param>
		public void AddXWhere(string xmlColumn, string path, Comparison comparison, object value)
		{
			this.AddXWhere(new XWhere(xmlColumn, path, comparison, value));
		}
		/// <summary>
		/// Adds a new Where clause to the current query. Used for parameterized queries on xml sql columns.
		/// </summary>
		/// <param name="where">An instance of an XWhere class used to query an Xml column.</param>
		public void AddXWhere(XWhere where)
		{
			_wheres.Add(where);
		}
		#endregion

		/// <summary>
		/// Adds the specified ORDER BY expression to the query, used for standard columns, not the XML type column.
		/// </summary>
		/// <param name="expression">The SQL ORDER BY expression to append to the query.</param>
		public void OrderBy(string expression)
		{
			if (expression.ToLower().Contains("order by"))
			{
				expression = expression.ToLower().Replace("order by", "");
			}
			_orderBys.Add(expression);
		}

		/// <summary>
		/// Adds an ORDER BY expression to the current query, used for standard columns, not the XML type column.
		/// </summary>
		/// <param name="direction">The ordering direction (ASC|DESC).</param>
		/// <param name="columnName">The name of the column in which to apply the ordering.</param>
		public void OrderBy(OrderByDirection direction, string columnName)
		{
			_orderBys.Add(String.Concat(columnName, " ", direction.ToString().ToUpper()));
		}

		/// <summary>
		/// Gets the string representation of this XQuery instance for executing in the database.
		/// </summary>
		/// <returns>A string of the XQuery structure for this instance.</returns>
		public override string ToString()
		{
			StringBuilder sb = new StringBuilder();

			if (this.DebugMode)
			{
				// TODO: Output sql declarations and variable sets to be able to copy and paste into
				// Sql Management Studio and execute.
			}

			sb.Append("SET ARITHABORT ON SELECT TOP ").Append(this.Top).Append(" ");
			sb.Append(_schema.SelectList.ToString());
			sb.Append(" FROM ");
			sb.Append(_schema.ToString());
			this.BuildWhereClause(sb);
			this.BuildOrderByClause(sb);

			return sb.ToString();
		}

		private void BuildWhereClause(StringBuilder sb)
		{
			if (_wheres.Count > 0)
			{
				sb.Append(" WHERE ");
				for (int i = 0; i < _wheres.Count; i++)
				{
					Where where = _wheres[i];
					where.Index = i;
					if (i > 0)
					{
						sb.Append(" AND ");
					}
					sb.Append(where.ToString());

					// Append SqlParam and set param value.
					string paramName = String.Concat("@value", i);
					SqlParameter param = _parameters.Find(new Predicate<SqlParameter>(
						delegate(SqlParameter p) 
						{
							return p.ParameterName == paramName;
						}));
					if (param == null)
					{
						_parameters.Add(SqlHelper.CreateInputParam(paramName, SqlDbType.VarChar, where.Value));
					}
				}
			}
		}

		private void BuildOrderByClause(StringBuilder sb)
		{
			if (_orderBys.Count > 0)
			{
				sb.Append(" ORDER BY ");
				for (int i = 0; i < _orderBys.Count; i++)
				{
					if (i > 0)
					{
						sb.Append(", ");
					}
					sb.Append(_orderBys[i]);
				}
			}
		}

		#region Properties
		private TableSchema _schema;	

		/// <summary>
		/// Gets the TableSchema instance for the current query.
		/// </summary>
		public TableSchema Schema
		{
			get { return _schema; }
		}

		private List<Where> _wheres = new List<Where>();

		/// <summary>
		/// Gets a list of the Where instances for this query.
		/// </summary>
		public List<Where> Wheres
		{
			get { return _wheres; }
		}

		private List<string> _orderBys = new List<string>();	

		/// <summary>
		/// Gets a list of the ORDER BY expressions used for this query.
		/// </summary>
		public List<string> OrderBys
		{
			get { return _orderBys; }
		}


		private List<SqlParameter> _parameters = new List<SqlParameter>();	

		/// <summary>
		/// Gets a list of the SqlParameters for this query.
		/// </summary>
		public List<SqlParameter> Parameters
		{
			get { return _parameters; }
		}
	
		private bool _debugMode = false;	

		/// <summary>
		/// Gets or sets a vlaue indicating whether or not the query should print debug information.
		/// </summary>
		public bool DebugMode
		{
			get { return _debugMode; }
			set { _debugMode = value; }
		}

		private string _top = "100 PERCENT";	

		/// <summary>
		/// Gets or sets the number or percentage of results to return.
		/// </summary>
		public string Top
		{
			get { return _top; }
			set { _top = value; }
		}
	
		#endregion
	}
	#endregion

	#region Where Class
	/// <summary>
	/// An enumeration of comparison values used to render dynamic XQueries.
	/// </summary>
	public enum Comparison
	{
		/// <summary>
		/// Represents the "=" sign in a sql statement.
		/// </summary>
		Equals,
		/// <summary>
		/// Represents the "!=" sign in a sql statement.
		/// </summary>
		NotEquals,
		/// <summary>
		/// Represents the "%" sign in a sql statement.
		/// </summary>
		StartsWith,
		/// <summary>
		/// Represents the "%" sign in a sql statement.
		/// </summary>
		EndsWith,
		/// <summary>
		/// Represents the "&gt;" sign in a sql statement.
		/// </summary>
		GreaterThan,
		/// <summary>
		/// Represents the "&lt;" sign in a sql statement.
		/// </summary>
		LessThan,
		/// <summary>
		/// Represents the "&gt;=" sign in a sql statement.
		/// </summary>
		GreaterThanOrEquals,
		/// <summary>
		/// Represents the "&lt;=" sign in a sql statement.
		/// </summary>
		LessThanOrEquals,
	}

	/// <summary>
	/// Represents a SQL Where Clause.
	/// </summary>
	public class Where
	{
		/// <summary>
		/// Initializes a new instance of the Where class.
		/// </summary>
		public Where()
			: this(null, Comparison.Equals, null)
		{
		}

		/// <summary>
		/// Initializes a new instance of the Where class.
		/// </summary>
		/// <param name="columnName">The name of the column in this where clause.</param>
		/// <param name="value">The value that will be compared in the where clause.</param>
		public Where(string columnName, object value)
			: this(columnName, Comparison.Equals, value)
		{
		}

		/// <summary>
		/// Initializes a new instance of the Where class.
		/// </summary>
		/// <param name="columnName">The name of the column in this where clause.</param>
		/// <param name="comparison">One of the Comparison values.</param>
		/// <param name="value">The value that will be compared in the where clause.</param>
		public Where(string columnName, Comparison comparison, object value)
		{
			_columnName = columnName;
			_comparison = comparison;
			_value = value;
		}

		/// <summary>
		/// Gets the string value of the Comparison enumeration type.
		/// </summary>
		/// <param name="comparison">One of the Comparison values.</param>
		/// <returns>The string representation of the Comparison value.</returns>
		public static string GetComparisonValue(Comparison comparison)
		{
			switch (comparison)
			{
				case Comparison.NotEquals: return "!=";
				case Comparison.StartsWith: return "%";
				case Comparison.EndsWith: return "%";
				case Comparison.GreaterThan: return ">";
				case Comparison.LessThan: return "<";
				case Comparison.GreaterThanOrEquals: return ">=";
				case Comparison.LessThanOrEquals: return "<=";
				case Comparison.Equals:
				default: return "=";
			}
		}

		/// <summary>
		/// Gets the string representation of this where clause.
		/// </summary>
		/// <returns>The string representation of this where clause.</returns>
		public override string ToString()
		{
			return String.Format("{0} {1} @value{2}", this.ColumnName, Where.GetComparisonValue(this.Comparison), this.Index);
		}
		
		#region Properties
		private string _columnName;

		/// <summary>
		/// Gets or sets the name of the column in the where clause.
		/// </summary>
		public string ColumnName
		{
			get { return _columnName; }
			set { _columnName = value; }
		}
	
		private Comparison _comparison;

		/// <summary>
		/// Gets or sets the Comparison value of the where clause.
		/// </summary>
		public Comparison Comparison
		{
			get { return _comparison; }
			set { _comparison = value; }
		}

		private object _value;

		/// <summary>
		/// Gets or sets the value that will be compared in this where clause.
		/// </summary>
		public object Value
		{
			get { return _value; }
			set { _value = value; }
		}

		private int _index = -1;	

		/// <summary>
		/// Gets or sets the index value of this where clause, used to auto generate parameter names.
		/// </summary>
		public int Index
		{
			get { return _index; }
			set { _index = value; }
		}
	
		#endregion
	}

	/// <summary>
	/// Represents a SQL Where Clause used to query an XML data type column.
	/// </summary>
	public class XWhere : Where
	{
		/// <summary>
		/// Initializes a new instance of the XWhere class.
		/// </summary>
		/// <param name="xmlColumn">The name of the column that is of type (xml).</param>
		public XWhere(string xmlColumn)
			: this(xmlColumn, null, Comparison.Equals, null)
		{
		}

		/// <summary>
		/// Initializes a new instance of the XWhere class.
		/// </summary>
		/// <param name="xmlColumn">The name of the column that is of type (xml).</param>
		/// <param name="xPath">The XPath query exper</param>
		/// <param name="value">The value to compare with the xml node value.</param>
		public XWhere(string xmlColumn, string xPath, object value)
			: this(xmlColumn, xPath, Comparison.Equals, value)
		{
		}

		/// <summary>
		/// Initializes a new instance of the XWhere class.
		/// </summary>
		/// <param name="xmlColumn">The name of the column that is of type (xml).</param>
		/// <param name="xPath">The XPath query to the node within the xml data type column to use for comparison.</param>
		/// <param name="comparison">One of the Comparison values.</param>
		/// <param name="value">The value to compare with the xml node value.</param>
		public XWhere(string xmlColumn, string xPath, Comparison comparison, object value)
			: base(xPath, comparison, value)
		{
			_xmlColumn = xmlColumn;
		}

		/// <summary>
		/// Gets the string value of the Comparison enumeration type.
		/// </summary>
		/// <param name="comparison">One of the Comparison values.</param>
		/// <returns>The string representation of the Comparison value.</returns>
		public static new string GetComparisonValue(Comparison comparison)
		{
			switch (comparison)
			{
				case Comparison.NotEquals: return "!=";
				case Comparison.GreaterThan: return ">";
				case Comparison.LessThan: return "<";
				case Comparison.GreaterThanOrEquals: return ">=";
				case Comparison.LessThanOrEquals: return "<=";
				case Comparison.Equals:
				default: return "=";
			}
		}

		/// <summary>
		/// Gets the string representation of this where clause.
		/// </summary>
		/// <returns>The string representation of this where clause.</returns>
		public override string ToString()
		{
			string path = this.ColumnName;

			if (!path.StartsWith("//"))
			{
				if (path.StartsWith("/"))
				{
					path = String.Concat("/", path);
				}
				else
				{
					path = String.Concat("//", path);
				}
			}
			if (path.EndsWith("/"))
			{
				path = path.Remove(path.Length - 1, 1);
			}
			StringBuilder sb = new StringBuilder();
			sb.Append(this.XmlColumn).Append(".exist('(").Append(path).Append("/text())[. ");
			sb.Append(XWhere.GetComparisonValue(this.Comparison)).Append(" sql:variable(");
			sb.Append('"').Append("@value").Append(this.Index).Append('"').Append(")]') = 1");
			return sb.ToString();
		}

		private string _xmlColumn;

		/// <summary>
		/// Gets or sets the name of the XML data type column used in this expression.
		/// </summary>
		public string XmlColumn
		{
			get { return _xmlColumn; }
			set { _xmlColumn = value; }
		}
	
	}
	#endregion

	#region TableSchema Class
	/// <summary>
	/// Represents a database table definition used in XQueries.
	/// </summary>
	public class TableSchema
	{
		/// <summary>
		/// Initializes a new instance of the TableSchema class.
		/// </summary>
		/// <param name="name">The name of the table.</param>
		public TableSchema(string name)
		{
			_name = name;
		}

		/// <summary>
		/// Initializes a new instance of the TableSchema class.
		/// </summary>
		/// <param name="name">The name of the table.</param>
		/// <param name="columns">The TableColumnCollection instance that represents the select list of the table.</param>
		public TableSchema(string name, TableColumnCollection columns)
			: this(name)
		{
			_selectList = columns;
		}

		/// <summary>
		/// Initializes a new instance of the TableSchema class.
		/// </summary>
		/// <param name="name">The name of the table.</param>
		/// <param name="columns">The TableColumn array that represents the select list of the table.</param>
		public TableSchema(string name, params TableColumn[] columns)
			: this (name, new TableColumnCollection(columns))
		{
		}

		/// <summary>
		/// Gets a string representation of the current table, including the owner name.
		/// </summary>
		/// <returns>The full table name such as dbo.Table.</returns>
		public override string ToString()
		{
			return String.Format("{0}.{1}", this.Owner, this.Name);
		}

		#region Properties
		private string _name;

		/// <summary>
		/// Gets or sets the name of the table.
		/// </summary>
		public string Name
		{
			get { return _name; }
			set { _name = value; }
		}

		private string _owner = "dbo";	

		/// <summary>
		/// Gets or sets the owner name of the table. Default is "dbo".
		/// </summary>
		public string Owner
		{
			get { return _owner; }
			set { _owner = value; }
		}
	
		private TableColumnCollection _selectList = new TableColumnCollection();	

		/// <summary>
		/// Gets the collection of TableColumn instances that represent the select list of columns for this Table.
		/// </summary>
		public TableColumnCollection SelectList
		{
			get { return _selectList; }
		}
	
		#endregion
	}
	#endregion

	#region TableColumn Class
	/// <summary>
	/// Represents a database table column definition.
	/// </summary>
	public class TableColumn
	{
		/// <summary>
		/// Initializes a new instance of the TableColumn class.
		/// </summary>
		/// <param name="name">The name of the column.</param>
		/// <param name="dataType">The SqlDbType value of the column.</param>
		public TableColumn(string name, SqlDbType dataType)
		{
			_name = name;
			_dataType = dataType;
		}

		/// <summary>
		/// Gets a string representation of the column, safe for sql statement use.
		/// </summary>
		/// <returns>The column name such as [ Column ].</returns>
		public override string ToString()
		{
			return String.Format("[{0}]", this.Name);
		}

		#region Properties
		private string _name;

		/// <summary>
		/// Gets or sets the name of the column.
		/// </summary>
		public string Name
		{
			get { return _name; }
			set { _name = value; }
		}

		private SqlDbType _dataType;	

		/// <summary>
		/// Gets or sets the SqlDbType value of the column.
		/// </summary>
		public SqlDbType DataType
		{
			get { return _dataType; }
			set { _dataType = value; }
		}
	
		#endregion
	}
	#endregion

	#region TableColumnCollection
	/// <summary>
	/// Provides a collection of TableColumn instances.
	/// </summary>
	public class TableColumnCollection : System.Collections.ObjectModel.Collection<TableColumn>
	{
		/// <summary>
		/// Initializes a new instance of the TableColumnCollection class.
		/// </summary>
		public TableColumnCollection()
		{
		}

		/// <summary>
		/// Initializes a new instance of the TableColumnCollection class.
		/// </summary>
		/// <param name="list">The IList of TableColumns to add to the collection.</param>
		public TableColumnCollection(IList<TableColumn> list)
			: base(list)
		{
		}

		/// <summary>
		/// Gets a string representation of the columns contained within. Suitable for a sql statement select list.
		/// </summary>
		/// <returns>The list of columns in the collection such as "[ Column1 ], [ Column2 ], [ Column(n) ]"</returns>
		public override string ToString()
		{
			StringBuilder sb = new StringBuilder();
			int count = this.Count;
			if (count > 0)
			{
				for (int i = 0; i < count; i++)
				{
					sb.Append(" ").Append(this[i].ToString());
					if (i < (count - 1))
						sb.Append(", ");
				}
			}
			else
			{
				sb.Append(" * ");
			}
			return sb.ToString();
		}
	}
	#endregion
}
